import pandas as pd
from sqlalchemy import create_engine, text

# 获取mysql的连接
conn = create_engine("mysql+pymysql://{}:{}@{}:3306/{}?".format("root","123456","localhost","person"))

sql = """
    CREATE TABLE user_info(
        id int(11) ,
        idcard varchar(50) ,
        username varchar(50) ,
        realname varchar(50) ,
        pwd varchar(50) ,
        telphone varchar(12) ,
        email varchar(100) ,
        age int(11) ,
        sex varchar(20) ,
        address varchar(200) ,
        hiredate date ,
        sal double(9,2) ,
        job varchar(100) ,
        company varchar(100)
    );
"""
conn.connect().execute(text(sql))
conn.connect().commit()

qu_sql = "SELECT * FROM person"
df = pd.read_excel(f"D:\\5.baidu-员工的人员信息.xls")
df.to_sql("user_info", conn, if_exists='replace', index=False)